import pandas as pd
import datetime
import numpy as np
import Core.Gadget as Gadget
import Core.WindFunctions as Wind


# Financial Report 是规律信息
# 这里处理处理非规律信息

# ---报表中的分红信息---
def Download_Dividend(database, datetime1, datetime2):

    print("Download Dividend From", datetime1.date(), "To", datetime2.date())

    # 可以按照报告期 / 公告日期下载下载数据
    # 公告日时间更准确
    # w.wset("bonus","orderby=报告期;year=2019;period=s3;sectorid=a001010100000000")
    # w.wset("bonus","orderby=实施公告日;startdate=2019-10-14;enddate=2020-04-14;sectorid=a001010100000000")

    params = {}
    params["orderby"] = "实施公告日"
    params["startdate"] = datetime1.date()
    params["enddate"] = datetime2.date()
    params["sectorid"] = "a001010100000000" #全部A股

    s = []
    for field, value in params.items():
        s.append(field + "=" + str(value))
    strParams = ";".join(s)

    # ---Request Functions---
    values = Wind.w.wset("bonus", strParams)

    # key: wind field, value: my field
    field_map = {}
    field_map["wind_code"] = "Symbol"
    field_map["reporting_date"] = "report_date"
    field_map["scheme_des"] = "description"
    field_map["progress"] = "progress"
    field_map["dividendsper_share_pretax"] = "dividend_cash_pretax"
    field_map["dividendsper_share_aftertax"] = "dividend_cash_aftertax"
    field_map["sharedividends_proportion"] = "dividend_stock_proportion"
    field_map["shareincrease_proportion"] = "dividend_stock_increase_proportion"
    field_map["share_benchmark"] = "shares_base"
    field_map["share_benchmark_date"] = "shares_base_date"
    field_map["dividends_announce_date"] = "Announce_Date"
    field_map["shareregister_date"] = "register_date"
    field_map["bshareregister_date"] = "b_register_date"
    field_map["exrights_exdividend_date"] = "ex_date"
    field_map["dividend_payment_date"] = "payment_date"
    field_map["redchips_listing_date"] = "listed_date"
    field_map["dividend_object"] = "dividend_to"

    list_df = []
    for field, map_to in field_map.items():
        # 找到 wind field 对应的 i 位置
        i = values.Fields.index(field)
        # 用 i 对应到Data下的列，再重命名
        df_tmp = pd.DataFrame(data=values.Data[i], columns=[map_to])
        list_df.append(df_tmp)
    #
    df = pd.concat(list_df, axis=1)

    #
    df["shares_base"] = df["shares_base"] * 10000

    print(df)

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if str(value) == 'NaT':
                newDocument[field] = None
            elif isinstance(value, float) and np.isnan(value):
                newDocument[field] = None

        #
        newDocument["Date"] = newDocument["Announce_Date"]
        newDocument["DateTime"] = newDocument["Announce_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" + Gadget.ToDateString(newDocument["Announce_Date"])
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "dividend", {}, newDocuments)
    print("Upsert Dividend Info from", datetime1, "to", datetime2, "#", len(newDocuments))


def Batch_Download_Dividend(database, begin_year, end_year):
    #
    for year in range(begin_year, end_year + 1):
        datetime1 = datetime.datetime(year, 1, 1)
        datetime2 = datetime.datetime(year + 1, 1, 1)
        print(datetime1, datetime2)
        Download_Dividend(database, datetime1, datetime2)


def Statistics_Dividend(database):
    # 历年分红家数比例
    for year in range(2000, 2021):
        datetime1 = datetime.datetime(year, 1, 1)
        datetime2 = datetime.datetime(year+1, 1, 1)
        instruments = Gadget.FindListedInstrument(database, datetime2=datetime2)
        documents = database.Find("stock", "dividend", filter=[("Date", ">=", datetime1.date()), ("Date", "<", datetime2.date())])
        #
        print(datetime1.date(), "Total", len(instruments), "# Pay Dividend", len(documents), "Ratio", len(documents)/len(instruments))

    # 平均股利支付率（现金分红 / 归母净利润）
    pass


def Statistics_Dividend_PayoutRatio(database):
    #
    # documents = database.Find("stock", "dividend",
    #                           filter={"symbol":"000001.SZ"})
    # df = Gadget.DocumentsToDataFrame(documents)
    # df["cash"] = df["dividend_cash_pretax"] * df["shares_base"]
    # print(df[["key2", "cash"]])

    def Print_Duplicated(df):
        # 完全去重
        data1 = df.drop_duplicates(subset=["symbol"], keep=False)
        # 有意造成重复
        df_tmp = df.append(data1)
        df_tmp = df_tmp.append(data1)
        df_tmp = df_tmp.drop_duplicates(keep=False)
        print(df_tmp)

    for year in range(2005, 2021):
        reportDate1 = datetime.datetime(year-1, 12, 31)
        reportDate2 = datetime.datetime(year, 12, 31)
        # instruments = Gadget.FindListedInstrument(database, datetime2=datetime2)
        documents = database.Find("stock", "dividend",
                                  filter=[("Report_Date", ">", reportDate1.date()), ("Report_Date", "<=", reportDate2.date())])
        #
        df = Gadget.DocumentsToDataFrame(documents)
        df_drop = df.drop_duplicates(subset=["symbol"])

        # 处理分红数据
        df["total_cash_dividend"] = df["dividend_cash_pretax"] * df["shares_base"]
        df_dividend = df.pivot_table(index='symbol', values=["total_cash_dividend", "report_date"], aggfunc={"total_cash_dividend": np.sum, "report_date": max})
        df_dividend["report_date"] = reportDate2
        # print(df_dividend)

        # 读取盈利信息
        #  filter={"report_date": reportDate2.date()}
        documents = database.Find("stock", "fundamental", filter={"report_date": reportDate2.date()}, projection=["symbol", "netincome2"])
        df_fundamental = Gadget.DocumentsToDataFrame(documents, index="symbol")
        # df_fundamental.set_index("symbol", inplace=True)
        # print(df_fundamental)
        if df_dividend.empty:
            continue
        #
        df_dividend = pd.merge(df_dividend, df_fundamental, how="left", on="symbol")
        df_dividend["Payout_Ratio"] = df_dividend["total_cash_dividend"] / df_dividend["netincome2"]
        # print(df_dividend)
        # if "000001.SZ" in df_dividend.index:
        #     print(year, df_dividend.loc["000001.SZ"])
        #
        mean = df_dividend["Payout_Ratio"].mean()
        median = df_dividend["Payout_Ratio"].median()
        print(year, "#", len(df), len(df_drop),
              "Payout_Ratio_Mean", mean,
              "Payout_Ratio_Median", median,
              "totalDiv", df_dividend["total_cash_dividend"].sum(),
              "mean", df_dividend["total_cash_dividend"].mean(),
              "median", df_dividend["total_cash_dividend"].median())

        a = 0


if __name__ == '__main__':
    #
    from Core.Config import *
    pathfilename = os.getcwd() + "\..\Config\config2.json"
    config = Config(pathfilename)
    database = config.DataBase("JDMySQL")
    logger = config.Logger("WindFundamental")

    #
    Wind.w.start()  # 启动Wind API
    # Test
    # Download_Dividend(database, datetime.datetime(2020,1,1), datetime.datetime(2020,2,1))

    Batch_Download_Dividend(database, 2019, 2020)
    #
    Statistics_Dividend(database)
    Statistics_Dividend_PayoutRatio(database)
